package com.example.cursor_orm.dao.impl;

import com.example.cursor_orm.dao.UserDAO;
import com.example.cursor_orm.model.User;
import com.example.cursor_orm.util.DatabaseUtil;
import org.springframework.stereotype.Repository;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Repository
public class UserDAOImpl implements UserDAO {
    
    @Override
    public int insert(User user) {
        String sql = "INSERT INTO user (username, password_hash, salt, real_name, user_type, student_no, staff_no, dept, grade, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        return DatabaseUtil.executeUpdate(sql, user.getUsername(), user.getPasswordHash(), user.getSalt(), 
                                       user.getRealName(), user.getUserType().toString(), user.getStudentNo(), 
                                       user.getStaffNo(), user.getDept(), user.getGrade(), 
                                       user.getCreatedAt());
    }
    
    @Override
    public int deleteById(Integer id) {
        String sql = "DELETE FROM user WHERE id = ?";
        return DatabaseUtil.executeUpdate(sql, id);
    }
    
    @Override
    public int update(User user) {
        String sql = "UPDATE user SET username = ?, password_hash = ?, salt = ?, real_name = ?, user_type = ?, student_no = ?, staff_no = ?, dept = ?, grade = ? WHERE id = ?";
        return DatabaseUtil.executeUpdate(sql, user.getUsername(), user.getPasswordHash(), user.getSalt(), 
                                       user.getRealName(), user.getUserType().toString(), user.getStudentNo(), 
                                       user.getStaffNo(), user.getDept(), user.getGrade(), 
                                       user.getId());
    }
    
    @Override
    public User findById(Integer id) {
        String sql = "SELECT * FROM user WHERE id = ?";
        ResultSet rs = DatabaseUtil.executeQuery(sql, id);
        return mapResultSetToUser(rs);
    }
    
    @Override
    public List<User> findAll() {
        String sql = "SELECT * FROM user ORDER BY id";
        ResultSet rs = DatabaseUtil.executeQuery(sql);
        return mapResultSetToUserList(rs);
    }
    
    @Override
    public List<User> findByCondition(String condition) {
        String sql = "SELECT * FROM user WHERE " + condition + " ORDER BY id";
        ResultSet rs = DatabaseUtil.executeQuery(sql);
        return mapResultSetToUserList(rs);
    }
    
    @Override
    public int count() {
        String sql = "SELECT COUNT(*) FROM user";
        ResultSet rs = DatabaseUtil.executeQuery(sql);
        try {
            if (rs != null && rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
    
    @Override
    public User findByUsername(String username) {
        String sql = "SELECT * FROM user WHERE username = ?";
        ResultSet rs = DatabaseUtil.executeQuery(sql, username);
        return mapResultSetToUser(rs);
    }



    @Override
    public boolean existsByUsername(String username) {
        String sql = "SELECT COUNT(*) FROM user WHERE username = ?";
        ResultSet rs = DatabaseUtil.executeQuery(sql, username);
        try {
            if (rs != null && rs.next()) {
                return rs.getInt(1) > 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
    
    @Override
    public User authenticate(String username, String passwordHash) {
        String sql = "SELECT * FROM user WHERE username = ? AND password_hash = ?";
        ResultSet rs = DatabaseUtil.executeQuery(sql, username, passwordHash);
        return mapResultSetToUser(rs);
    }
    
    @Override
    public List<User> findByGrade(String grade) {
        String sql = "SELECT * FROM user WHERE grade = ? ORDER BY id";
        ResultSet rs = DatabaseUtil.executeQuery(sql, grade);
        return mapResultSetToUserList(rs);
    }
    
    /**
     * 将ResultSet映射为User对象
     */
    private User mapResultSetToUser(ResultSet rs) {
        try {
            if (rs != null && rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPasswordHash(rs.getString("password_hash"));
                user.setSalt(rs.getString("salt"));
                user.setRealName(rs.getString("real_name"));
                user.setUserType(User.UserType.valueOf(rs.getString("user_type")));
                user.setStudentNo(rs.getString("student_no"));
                user.setStaffNo(rs.getString("staff_no"));
                user.setDept(rs.getString("dept"));
                user.setGrade(rs.getString("grade"));
                user.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    
    /**
     * 将ResultSet映射为User对象列表
     */
    private List<User> mapResultSetToUserList(ResultSet rs) {
        List<User> users = new ArrayList<>();
        try {
            if (rs != null) {
                while (rs.next()) {
                    User user = new User();
                    user.setId(rs.getInt("id"));
                    user.setUsername(rs.getString("username"));
                    user.setPasswordHash(rs.getString("password_hash"));
                    user.setSalt(rs.getString("salt"));
                    user.setRealName(rs.getString("real_name"));
                    user.setUserType(User.UserType.valueOf(rs.getString("user_type")));
                    user.setStudentNo(rs.getString("student_no"));
                    user.setStaffNo(rs.getString("staff_no"));
                    user.setDept(rs.getString("dept"));
                    user.setGrade(rs.getString("grade"));
                    user.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
                    users.add(user);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return users;
    }
} 